1. Field of the Invention
This invention relates to query execution plans and more particularly relates to managing query execution plans for a workload.
2. Description of the Related Art
A database administrator (“DBA”) is often concerned with groups of database statements rather than single individual database statements. These groups of database statements are referred to as database workloads. The DBA is often concerned with maximizing the execution performance of certain workloads and must ensure that the execution of the workload by the Database management system (“DBMS”) obtains a certain level of performance. A DBA is typically interested in the query execution plans (“QEP”s) created by the DBMS for database statements in the workload.
DBMSs such as DB2, Oracle, IMS, and MYSQL often create query execution plans (“QEP”s) to optimize the execution of database queries. A QEP identifies a set of indexes, tables, caches, and other optimization aides that the DBMS references each time it executes a database query associated with the QEP. A QEP associated with a request may change based upon the information that the DBMS has about the data such as statistics, other physical schema changes, or updated code of the optimizer. Usually, these changes result in a more efficient QEP and greater performance. However, sometimes the database query performs worse under the new QEP.
The time and effort required to manage QEPs increases greatly when a DBA is monitoring one or more workloads. Some DBMSs allow the DBA to provide “hints” that guide the DBMS in QEP creation for statements in the workload. However, relying on the DBA to provide hints requires expertise by the DBA to understand how to influence the QEP creation. In addition, hints cannot always be used to get the exact QEP desired. Moreover, creating and maintaining hints are time-consuming tasks especially at the workload level.